﻿
-- =============================================
-- Author:		zengfei
-- Create date: 09m/26/2011
-- Description:	操作SubjectMessage
-- =============================================
Create PROCEDURE [dbo].[Usp_ModifySubjectMessage]
	@p_Action	char,				-- A:新增, C:修改, D:删除
	@p_SubjectID	int out, 
	@p_SubjectCode	varchar(30),
	@p_SubjectName	varchar(100), 
	@p_ParentID	int, 
	@p_Disabled	bit, 
	@p_DetailFlag	bit, 
	@p_Description	varchar(200),
	@p_ErrMsg	varchar(100) out,
	@p_RFlag	smallint out
AS
if	@p_Action = 'A'
begin
	if	exists (select * from SubjectMessage where ParentID = @p_ParentID and (SubjectCode = @p_SubjectCode or SubjectName = @p_SubjectName))
	begin
		raiserror('科目编码[%s]或科目名称[%s]重复!', 16, 1, @p_SubjectCode, @p_SubjectName)
		goto ErrHandle
	end
	
	insert into SubjectMessage (SubjectCode, SubjectName, ParentID, [Disabled], DetailFlag, [Description])
		values (@p_SubjectCode, @p_SubjectName, @p_ParentID, @p_Disabled, @p_DetailFlag, @p_Description)
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	select @p_SubjectID = @@IDENTITY 
end
else if	@p_Action = 'C'
begin
	if	exists (select * from SubjectMessage where ParentID = @p_ParentID and SubjectID <> @p_SubjectID and (SubjectCode = @p_SubjectCode or SubjectName = @p_SubjectName))
	begin
		raiserror('科目编码[%s]或科目名称[%s]重复!', 16, 1, @p_SubjectCode, @p_SubjectName)
		goto ErrHandle
	end
	
	update SubjectMessage set SubjectName = @p_SubjectName, 
						[Disabled] = @p_Disabled, 
						DetailFlag = @p_DetailFlag, 
						[Description] = @p_Description
					where SubjectID = @p_SubjectID 
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
end
else if	@p_Action = 'D'
begin
	if  exists (select * from SubjectMessage where SubjectID = @p_SubjectID and DetailFlag = 1)
	begin
		raiserror('科目[%s][%s]存在子项明细, 不能删除!', 16, 1, @p_SubjectCode, @p_SubjectName)
		goto ErrHandle
	end
	
	Delete from SubjectMessage where SubjectID = @p_SubjectID 
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
end

set @p_RFlag = 1

ErrHandle:
set @p_RFlag = -1

